import pymysql
from pymysql.cursors import DictCursor

def Insert():
    name = input('name: ')
    addr_type = input('addr_type: ')
    key = input('key: ')

    # 1. 创建连接（Connection）
    conn = pymysql.connect(host='127.0.0.1', port=3306,
                        user='root', password='root',
                        database='configuration', charset='utf8mb4')
    try:
        # 2. 获取游标对象（Cursor）
        with conn.cursor() as cursor:
            # 3. 通过游标对象向数据库服务器发出SQL语句
            affected_rows = cursor.execute(
                'insert into `addr` (`name`,`addr_type`,`key`) values (%s, %s, %s)',
                (name, addr_type, key)
            )
            if affected_rows == 1:
                print('操作成功!')
        # 4. 提交事务（transaction）
        conn.commit()
    except pymysql.MySQLError as err:
        # 4. 回滚事务
        conn.rollback()
        print("sql error: ",err)
        # print(type(err), err)
    finally:
        # 5. 关闭连接释放资源
        conn.close()

def Delete():
    id = int(input('id: '))
    conn = pymysql.connect(host='127.0.0.1', port=3306,
                        user='root', password='root',
                        database='configuration', charset='utf8mb4')
    try:
        with conn.cursor() as cursor:
            affected_rows = cursor.execute(
                'DELETE FROM `addr` WHERE `id`=%s',
                (id)
            )
            if affected_rows == 1:
                print('操作成功! Delete')
        conn.commit()
    except pymysql.MySQLError as err:
        conn.rollback()
        print("sql error: ",err)
    finally:
        conn.close()


def Update():
    id = int(input('id: '))
    name = input('name: ')
    conn = pymysql.connect(host='127.0.0.1', port=3306,
                        user='root', password='root',
                        database='configuration', charset='utf8mb4')
    try:
        with conn.cursor() as cursor:
            affected_rows = cursor.execute(
                'UPDATE `addr` SET `name`=%s WHERE `id`=%s',
                (name,id)
            )
            if affected_rows == 1:
                print('操作成功! Update')
        conn.commit()
    except pymysql.MySQLError as err:
        conn.rollback()
        print("sql error: ",err)
    finally:
        conn.close()


def Select():
    conn = pymysql.connect(host='127.0.0.1', port=3306,user='root', password='root',database='configuration', charset='utf8mb4')
    try:
        with conn.cursor(DictCursor) as cursor:
            cursor.execute('select `id`,`name`,`addr_type`,`key` from `addr`')
            row = cursor.fetchone()
            while row:
                print(row)
                row = cursor.fetchone()
    except pymysql.MySQLError as err:
        print("sql error: ",err)
    finally:
        conn.close()


if __name__ == '__main__':
    while True:
        print('------ \n模式: 1. insert 2. delete 3. update 4. select')
        operation = input('选择: ') # insert/delete/update/select
        if operation == 'insert' or operation == '1':
            Insert()
        elif operation == 'delete' or operation == '2':
            Delete()
        elif operation == 'update' or operation == '3':
            Update()
        elif operation == 'select' or operation == '4':
            Select()
        else:
            print('operation error!')
            break
